/***********************************************************************************************************************************
 * Name:	Database_Offline.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Report offline databases.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
GO
IF EXISTS(SELECT 0 FROM sys.databases WHERE [state] NOT IN (0,1))
  EXEC msdb.dbo.sp_send_dbmail
	@profile_name			= 'SQL Server Service',
	@recipients				= 'dbanotifications@us.bbaaviation.com',
	@subject				= 'Offline Database Report',
	@importance				= 'High',
	@query					= 'SELECT [Status/Name]=RTRIM([state_desc]+CHAR(9)+[name] COLLATE SQL_Latin1_General_CP1_CI_AS) FROM sys.databases WHERE [state] NOT IN (0,1) ORDER BY [database_id] DESC',
	@query_result_no_padding= 1,
	@query_result_separator	= '	';
GO